SET NOCOUNT ON
USE Northgale
go
--===========================================================================
-- This is almost the same as the previous script, but we begin a 
-- transaction and restart it for every 1000 rows.
EXEC setup_discounts

-- These are variables to monitor the solution.
DECLARE @rows          int = 0,
        @starttime     datetime2(3) = sysdatetime(),
        @afterms       int

DECLARE @OrderID       int,
        @Freight       decimal(10,2),
        @CustomerID    nchar(5),
        @OldDiscount   decimal(5,2),
        @Country       nvarchar(15),
        @City          nvarchar(25),
        @NewDiscount   decimal(5,2),
        @ProductAmount decimal(10,2),
        @OrderAmount   decimal(10,2)

CREATE TABLE #tmporders (OrderID     int           NOT NULL PRIMARY KEY,
                         CustomerID  nchar(5)      NOT NULL,
                         Freight     decimal(10,2) NOT NULL,
                         Discount    decimal(5,2)  NOT NULL)

INSERT #tmporders(OrderID, CustomerID, Freight, Discount)
   SELECT O.OrderID, O.CustomerID, O.Freight, O.Discount
   FROM   dbo.Orders O
   WHERE  O.OrderID IN (SELECT M.OrderID FROM dbo.OrdersToModify() M)

-- Start a transaction, this removes the need to harden the log for
-- each update.
BEGIN TRANSACTION

-- Start loop.
SELECT @OrderID = 0
WHILE 1 = 1
BEGIN
   SELECT @OrderID = MIN(OrderID) 
   FROM   #tmporders 
   WHERE  OrderID > @OrderID

   IF @OrderID IS NULL
      BREAK
   
   SELECT @CustomerID = CustomerID, @Freight = Freight, 
          @OldDiscount = Discount
   FROM   #tmporders
   WHERE  OrderID = @OrderID

   SELECT @Country = Country, @City = City 
   FROM   dbo.Customers
   WHERE  CustomerID = @CustomerID

   -- Determine discount.
   IF EXISTS (SELECT * 
              FROM   dbo.Discounts 
              WHERE  CustomerID = @CustomerID)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID = @CustomerID
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  City = @City)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  City = @City
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  Country = @Country)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  Country = @Country
   END 
   ELSE IF EXISTS (SELECT *
                   FROM  Discounts
                   WHERE CustomerID IS NULL
                     AND City       IS NULL
                     AND Country    IS NULL) 
   BEGIN 
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID IS NULL
        AND  City       IS NULL
        AND  Country    IS NULL 
   END
   ELSE 
      SELECT @NewDiscount = 0

   IF @NewDiscount <> @OldDiscount
   BEGIN
      SELECT @ProductAmount = SUM(UnitPrice * Quantity)
      FROM   dbo.[Order Details]
      WHERE  OrderID = @OrderID

      SELECT @OrderAmount = @ProductAmount * (1 - @NewDiscount/100) + 
                            @Freight

      UPDATE dbo.Orders
      SET    Discount    = @NewDiscount,
             TotalAmount = @OrderAmount
      WHERE  OrderID = @OrderID
   END

   -- Here we monitor the progress - but also commit and restart the transaction
   -- to avoid penalty for transaction that is open too long.
   SELECT @rows += 1
   IF @rows % 1000 = 0
   BEGIN
      COMMIT TRANSACTION
      BEGIN TRANSACTION

      SELECT @afterms = datediff(ms, @starttime, sysdatetime())
      RAISERROR('%d rows processed in %d ms.', 0, 1, 
                 @rows, @afterms) WITH NOWAIT
   END
END
-- Don't forget final commit!
COMMIT TRANSACTION

SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('%d rows processed in %d ms.', 0, 1, 
          @rows, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders
